drop table if exists jms_dm.dm_customer_province_bill_amount_temp_dt;

create external table jms_dm.dm_customer_province_bill_amount_temp_dt
(
pick_network_code string comment '网点编码',
pick_network_name string comment '网点名称',
first_franchisee_code string comment '所属加盟商编码',
first_franchisee_name string comment '所属加盟商name',
send_financial_center_code string comment '所属财务中心code',
send_financial_center_name string comment '所属财务中心名称',
date_time date comment '日期',
customer_code string comment '客户Code',
customer_name string comment '客户名称',
receiver_province_id string comment '目的省份ID',
receiver_province_name string comment '目的省份名称',
total_bill_count decimal(16,2) comment '总票数',
total_weight_count decimal(16,2) comment '总重量（内部计费总量）',
total_average_weight decimal(16,2) comment '均重（总重量/总票数）',
total_freight decimal(16,2) comment '收入（总运费）',
total_transfer_fee decimal(16,2) comment '中转费',
total_operation_fee decimal(16,2) comment '操作费',
total_base_dispatch_fee decimal(16,2) comment '基础派费',
total_piece_fee decimal(16,2) comment '续重派费',
total_sales_price decimal(16,2) comment '面单费',
total_bill_amount decimal(16,2) comment '取件提成',
total_prime_cost decimal(16,2) comment '成本',
total_profit decimal(16,2) comment '利润',
total_average_profit decimal(16,2) comment '均利（利润/票数）',
total_average_prime decimal(16,2) comment '单票成本(成本/总票数)',
zero_bill_count decimal(16,2) comment 'w=0票数',
zero_weight decimal(16,2) comment 'w=0重量',
zero_average_weight decimal(16,2) comment 'w=0均重',
zero_freight decimal(16,2) comment 'w=0收入',
zero_prime_cost decimal(16,2) comment 'w=0成本',
zero_profit decimal(16,2) comment 'w=0利润',
zero_average_profit decimal(16,2) comment 'w=0均利（利润/票数）',
one_bill_count decimal(16,2) comment 'w>0 w<=0.5票数',
one_weight decimal(16,2) comment 'w>0 w<=0.5重量',
one_average_weight decimal(16,2) comment 'w>0 w<=0.5均重',
one_freight decimal(16,2) comment 'w>0 w<=0.5收入',
one_prime_cost decimal(16,2) comment 'w>0 w<=0.5成本',
one_profit decimal(16,2) comment 'w>0 w<=0.5利润',
one_average_profit decimal(16,2) comment 'w>0 w<=0.5均利（利润/票数）',
two_bill_count decimal(16,2) comment 'w>0.5 w<=3.2票数',
two_weight decimal(16,2) comment 'w>0.5 w<=3.2重量',
two_average_weight decimal(16,2) comment 'w>0.5 w<=3.2均重',
two_freight decimal(16,2) comment 'w>0.5 w<=3.2收入',
two_prime_cost decimal(16,2) comment 'w>0.5 w<=3.2成本',
two_profit decimal(16,2) comment 'w>0.5 w<=3.2利润',
two_average_profit decimal(16,2) comment 'w>0.5 w<=3.2均利（利润/票数）',
three_bill_count decimal(16,2) comment 'w>3.2 w<=5.2票数',
three_weight decimal(16,2) comment 'w>3.2 w<=5.2重量',
three_average_weight decimal(16,2) comment 'w>3.2 w<=5.2均重',
three_freight decimal(16,2) comment 'w>3.2 w<=5.2收入',
three_prime_cost decimal(16,2) comment 'w>3.2 w<=5.2成本',
three_profit decimal(16,2) comment 'w>3.2 w<=5.2利润',
three_average_profit decimal(16,2) comment 'w>3.2 w<=5.2均利（利润/票数）',
four_bill_count decimal(16,2) comment 'w>5.2 w<=10.3票数',
four_weight decimal(16,2) comment 'w>5.2 w<=10.3重量',
four_average_weight decimal(16,2) comment 'w>5.2 w<=10.3均重',
four_freight decimal(16,2) comment 'w>5.2 w<=10.3收入',
four_prime_cost decimal(16,2) comment 'w>5.2 w<=10.3成本',
four_profit decimal(16,2) comment 'w>5.2 w<=10.3利润',
four_average_profit decimal(16,2) comment 'w>5.2 w<=10.3均利（利润/票数）',
five_bill_count decimal(16,2) comment 'w>10.3票数',
five_weight decimal(16,2) comment 'w>10.3重量',
five_average_weight decimal(16,2) comment 'w>10.3均重',
five_freight decimal(16,2) comment 'w>10.3收入',
five_prime_cost decimal(16,2) comment 'w>10.3成本',
five_profit decimal(16,2) comment 'w>10.3利润',
five_average_profit decimal(16,2) comment 'w>10.3均利（利润/票数）',
six_bill_count decimal(16,2) comment '0.5＜W≤1票数',
six_weight decimal(16,2) comment '0.5＜W≤1重量',
six_average_weight decimal(16,2) comment '0.5＜W≤1均重',
six_freight decimal(16,2) comment '0.5＜W≤1收入',
six_prime_cost decimal(16,2) comment '0.5＜W≤1成本',
six_profit decimal(16,2) comment '0.5＜W≤1利润',
six_average_profit decimal(16,2) comment '0.5＜W≤1均利',
seven_bill_count decimal(16,2) comment '1＜W≤2票数',
seven_weight decimal(16,2) comment '1＜W≤2重量',
seven_average_weight decimal(16,2) comment '1＜W≤2均重',
seven_freight decimal(16,2) comment '1＜W≤2收入',
seven_prime_cost decimal(16,2) comment '1＜W≤2成本',
seven_profit decimal(16,2) comment '1＜W≤2利润',
seven_average_profit decimal(16,2) comment '1＜W≤2均利',
eight_bill_count decimal(16,2) comment '2＜W≤3.2票数',
eight_weight decimal(16,2) comment '2＜W≤3.2重量',
eight_average_weight decimal(16,2) comment '2＜W≤3.2均重',
eight_freight decimal(16,2) comment '2＜W≤3.2收入',
eight_prime_cost decimal(16,2) comment '2＜W≤3.2成本',
eight_profit decimal(16,2) comment '2＜W≤3.2利润',
eight_average_profit decimal(16,2) comment '2＜W≤3.2均利',
nine_bill_count decimal(16,2) comment '5.2＜W≤20票数',
nine_weight decimal(16,2) comment '5.2＜W≤20重量',
nine_average_weight decimal(16,2) comment '5.2＜W≤20均重',
nine_freight decimal(16,2) comment '5.2＜W≤20收入',
nine_prime_cost decimal(16,2) comment '5.2＜W≤20成本',
nine_profit decimal(16,2) comment '5.2＜W≤20利润',
nine_average_profit decimal(16,2) comment '5.2＜W≤20均利',
ten_bill_count decimal(16,2) comment 'W>20票数',
ten_weight decimal(16,2) comment 'W>20重量',
ten_average_weight decimal(16,2) comment 'W>20均重',
ten_freight decimal(16,2) comment 'W>20收入',
ten_prime_cost decimal(16,2) comment 'W>20成本',
ten_profit decimal(16,2) comment 'W>20利润',
ten_average_profit decimal(16,2) comment 'W>20均利',
if_in_province_bill int comment '是否省内件',
total_with_freight bigint comment '有运费票数',
total_without_freight bigint comment '无运费票数',
cust_level string comment '客户等级'
)
COMMENT '网点客户目的省份预估财务日表'
PARTITIONED BY (dt STRING COMMENT '分区日期')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_customer_province_bill_amount_temp_dt'
TBLPROPERTIES (
    'discover.partitions' = 'false',
    'parquet.column.index.access' = 'true'
    );



ALTER TABLE jms_dm.dm_customer_province_bill_amount_temp_dt add COLUMNS(
ordersource_name string COMMENT "订单来源名称"
)cascade;



ALTER TABLE jms_dm.dm_customer_province_bill_amount_temp_dt add COLUMNS(
ten_bill_count decimal(16,2) comment 'W>20票数',
ten_weight decimal(16,2) comment 'W>20重量',
ten_average_weight decimal(16,2) comment 'W>20均重',
ten_freight decimal(16,2) comment 'W>20收入',
ten_prime_cost decimal(16,2) comment 'W>20成本',
ten_profit decimal(16,2) comment 'W>20利润',
ten_average_profit decimal(16,2) comment 'W>20均利',
ten_bill_count decimal(16,2) comment 'W>20票数',
ten_weight decimal(16,2) comment 'W>20重量',
ten_average_weight decimal(16,2) comment 'W>20均重',
ten_freight decimal(16,2) comment 'W>20收入',
ten_prime_cost decimal(16,2) comment 'W>20成本',
ten_profit decimal(16,2) comment 'W>20利润',
ten_average_profit decimal(16,2) comment 'W>20均利',
)cascade;
